import { sqlUtil } from '../common/db.js';

const StudentModel = {
  // Get student list with search functionality
  async getList(query, page) {
    let sql = `
      SELECT StudentID, Name, Email, Photo, Bio, Interests, Courses, Achievements 
      FROM Students 
      WHERE IsDeleted = 0
    `;
    let params = [];
    
    // Add search conditions
    if (query.keyword) {
      sql += ` AND (
        LOWER(Name) LIKE ? OR 
        LOWER(Email) LIKE ? OR
        LOWER(Interests) LIKE ? OR 
        LOWER(Courses) LIKE ? OR 
        LOWER(Bio) LIKE ?
      )`;
      const keyword = `%${query.keyword.toLowerCase()}%`;
      params = Array(5).fill(keyword);
    }
    
    sql += ` ORDER BY CreateTime DESC`;
    return sqlUtil.getLimit(sql, params, page);
  },

  // Get student by ID
  async getById(studentId) {
    const query = `SELECT * FROM Students WHERE StudentID = ? AND IsDeleted = 0`;
    return sqlUtil.get(query, [studentId]);
  },

  // Get student by email
  async getByEmail(email) {
    const query = `SELECT * FROM Students WHERE Email = ? AND IsDeleted = 0`;
    return sqlUtil.get(query, [email]);
  },

  // Create student record with default avatar
  async insert(student) {
    const defaultPhotoPath = '/uploads/default_photo.jpg';
    const query = `
      INSERT INTO Students (
        Name, Email, Password, Photo, Bio, 
        Interests, Courses, Achievements, IsDeleted
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0)
    `;
    return sqlUtil.run(query, [
      student.name,
      student.email,
      student.password,
      defaultPhotoPath,
      student.bio || null,
      student.interests || null,
      student.courses || null,
      student.achievements || null
    ]);
  },

  // Update student profile (all fields)
  async update(student) {
    const query = `
      UPDATE Students 
      SET Name = ?,
          Bio = ?,
          Photo = ?,
          Interests = ?,
          Courses = ?,
          Achievements = ?
      WHERE StudentID = ? AND IsDeleted = 0
    `;
    return sqlUtil.run(query, [
      student.name,
      student.bio,
      student.photo,
      student.interests,
      student.courses,
      student.achievements,
      student.studentId
    ]);
  },

  // Update student password
  async updatePassword(studentId, password) {
    const query = `UPDATE Students SET Password = ? WHERE StudentID = ? AND IsDeleted = 0`;
    return sqlUtil.run(query, [password, studentId]);
  },

  // Delete student (soft delete)
  async delete(studentId) {
    const query = `UPDATE Students SET IsDeleted = 1 WHERE StudentID = ?`;
    return sqlUtil.run(query, [studentId]);
  }
};

export default StudentModel;